/*
 * Copyright (c) 2017,安徽皖通科技股份有限公司 All rights reserved.
 */

package com.wttech.dzzf.process.dao;

import com.wtkj.dao.impl.ListResultSetExtractor;
import com.wtkj.vo.dzzf.base.kfwd.FundAccount;
import com.wttech.dzzf.process.model.errordata.HCBPhone;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Optional;

/**
 * 错误证件数据DAO实现
 * Created by MaShibo on 2017/10/25.
 */
@Repository
public class ErrorCertDataDao {

    @Resource(name = "jdbcTemplate")
    private JdbcTemplate testJdbcTemplate;

    @Resource(name = "dzzfJdbcTemplate")
    private JdbcTemplate dzzfJdbcTemplate;

    public FundAccount queryFundAccountByCardNo(String cardNo) {
        final String sql =
                "SELECT F.* " +
                        "  FROM AC_CARDINFO C " +
                        "  JOIN AC_ACCOUNT T " +
                        "    ON C.ACCOUNTID = T.ACCOUNTID " +
                        "  JOIN AC_FUNDACCOUNT F " +
                        "    ON T.FUNDACCOUNTID = F.FUNDACCOUNTID " +
                        " WHERE C.ECARDNO = ?";
        List<FundAccount> fundAccounts = (List<FundAccount>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new FundAccount()), cardNo);
        return fundAccounts.isEmpty() ? null : fundAccounts.get(0);
    }

    public int updateFundAccountInfos(FundAccount fundAccount) {
        final String sql =
                "UPDATE AC_FUNDACCOUNT A " +
                        "   SET A.FUNDACCOUNTNAME = ?, " +
                        "       A.CERTIFITYPE     = ?, " +
                        "       A.OWNERTYPE       = ?, " +
                        "       A.CERTIFINO       = ? " +
                        " WHERE A.FUNDACCOUNTID = ?";
        return testJdbcTemplate.update(sql, fundAccount.getFundAccountName(), fundAccount.getCertifiType(), fundAccount.getOwnerType(), fundAccount.getCompanyLic(),
                fundAccount.getFundAccountId());
    }

    public Optional<String> queryFundAccountIdByCardNo(String cardNo) {
        final String sql =
                "SELECT F.FUNDACCOUNTID " +
                        "  FROM AC_CARDINFO C " +
                        "  JOIN AC_ACCOUNT T " +
                        "    ON C.ACCOUNTID = T.ACCOUNTID " +
                        "  JOIN AC_FUNDACCOUNT F " +
                        "    ON T.FUNDACCOUNTID = F.FUNDACCOUNTID " +
                        " WHERE C.ECARDNO = ?";
        List<String> ids = dzzfJdbcTemplate.query(sql, new RowMapper<String>() {
            @Override
            public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getString("FUNDACCOUNTID");
            }
        }, cardNo);
        return ids.isEmpty() ? Optional.empty() : Optional.of(ids.get(0));
    }

    public int updatePhoneByFundAccountId(String phoneNo, String fundAccountId) {
        return testJdbcTemplate.update("UPDATE AC_FUNDACCOUNT T SET T.PHONE=? WHERE T.FUNDACCOUNTID=? ", phoneNo, fundAccountId);
    }

    public Integer queryHCBPhoneCount(){
        return testJdbcTemplate.queryForObject("SELECT COUNT(1) FROM BS_HCBPHONE ", Integer.class);
    }

    public List<HCBPhone> queryHCBPhoneInfos(int startRow, int endRow){
        final String sql =
                "SELECT * " +
                        "  FROM (SELECT A.*, ROWNUM AS RM FROM BS_HCBPHONE A) " +
                        " WHERE RM >= ? " +
                        "   AND RM <= ?";
        return testJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(HCBPhone.class), startRow, endRow);
    }
}
